# controllers/order_controller.py
import os
import csv
from io import StringIO
from datetime import datetime, timedelta
import datetime as dt
from flask import render_template, request, redirect, url_for, send_file, make_response, jsonify, flash
from sqlalchemy import text, func
from models.database import SessionLocal
from models.order import Order, StatusHistory, SyncLog
from controllers.user_auth import requires_auth, requires_role
from services.order_service import get_country_name, format_phone_number

class OrderController:
    def __init__(self, app):
        self.app = app
        self.register_routes()
    
    def register_routes(self):
        """注册路由"""
        self.app.route('/orders')(self.index)
        self.app.route('/order/<int:order_id>')(self.order_detail)
        self.app.route('/export')(self.export_csv)
        self.app.route('/api/orders')(self.api_orders)
        self.app.route('/add-order', methods=['GET', 'POST'])(self.add_order)  # 添加添加订单路由
    
    @requires_auth
    def index(self):
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 检查用户是否有添加订单的权限
            can_add = current_user and current_user.can_add_order() if current_user else False
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 获取订单总数
            total_orders = query.count()
            
            # 计算分页信息
            total_pages = (total_orders + per_page - 1) // per_page
            page = max(1, min(page, total_pages))  # 确保页码在有效范围内
            offset = (page - 1) * per_page
            
            # 分页查询订单
            orders = query.order_by(Order.order_date.desc()).offset(offset).limit(per_page).all()
            
            # 为每个订单提取物流信息和商品详情
            for order in orders:
                # 提取运单物流数据
                tracking_data = []
                order_items = []
                if order.item_details:
                    try:
                        import json
                        from collections import defaultdict
                        items = json.loads(order.item_details)
                        
                        # 使用字典来合计同一商品的数量
                        item_summary = defaultdict(lambda: {'name': '', 'total_quantity': 0, 'meta_data': []})
                        
                        for item in items:
                            # 检查是否是专门用于存储物流信息的虚拟商品项
                            if item.get('name') == '物流信息' and 'meta_data' in item:
                                for meta in item['meta_data']:
                                    if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                        # 直接解析物流信息
                                        tracking_info_value = meta.get('value', '[]')
                                        # 检查是否已经是解析后的对象而不是JSON字符串
                                        if isinstance(tracking_info_value, str):
                                            tracking_info = json.loads(tracking_info_value)
                                        else:
                                            tracking_info = tracking_info_value
                                        
                                        if isinstance(tracking_info, list):
                                            tracking_data.extend(tracking_info)
                                        elif isinstance(tracking_info, dict):
                                            tracking_data.append(tracking_info)
                            # 处理普通商品项
                            else:
                                # 获取商品ID（使用商品名称作为ID，因为原始数据中可能没有真正的ID）
                                item_id = item.get('name', '') + '_' + str(item.get('id', ''))
                                
                                # 合计数量
                                item_summary[item_id]['name'] = item.get('name', '')
                                item_summary[item_id]['total_quantity'] += item.get('quantity', 0)
                                
                                # 收集口味等元数据（只保留第一次出现的）
                                if 'meta_data' in item and not item_summary[item_id]['meta_data']:
                                    for meta in item['meta_data']:
                                        # 提取口味信息（支持更多口味字段）
                                        if meta.get('key') in ['pa_taste', '口味', 'taste', 'pa_flavors', 'flavors', 'pa_flavor', 'flavor', 'pa_smaki', 'smaki', 'pa_smak', 'smak']:
                                            item_summary[item_id]['meta_data'].append({
                                                'key': meta.get('key', ''),
                                                'value': meta.get('display_value', meta.get('value', ''))
                                            })
                                
                                # 提取物流信息
                                if 'meta_data' in item:
                                    for meta in item['meta_data']:
                                        if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                            tracking_info = json.loads(meta.get('value', '[]'))
                                            if isinstance(tracking_info, list):
                                                tracking_data.extend(tracking_info)
                                            elif isinstance(tracking_info, dict):
                                                tracking_data.append(tracking_info)
                        
                        # 转换为列表格式
                        for item_id, item_info in item_summary.items():
                            order_items.append({
                                'name': item_info['name'],
                                'quantity': item_info['total_quantity'],
                                'meta_data': item_info['meta_data']
                            })
                    except Exception as e:
                        print(f"解析订单数据时出错: {e}")
                
                # 将tracking_data和items添加到订单对象中
                order.tracking_data = tracking_data
                order.items = order_items
            
            # 修复SQLAlchemy文本查询问题
            status_stats_query = "SELECT status, COUNT(*) as count FROM orders"
            status_stats_params = {}
            
            # 根据用户权限添加WHERE条件
            if current_user and current_user.role == "user" and current_user.country:
                status_stats_query += " WHERE customer_country = :country"
                status_stats_params['country'] = current_user.country
            
            status_stats_query += " GROUP BY status ORDER BY count DESC"
            status_stats = db.execute(text(status_stats_query), status_stats_params).fetchall()
            
            # 获取唯一的来源站点列表
            source_query = db.query(Order.source).distinct().order_by(Order.source)
            if current_user and current_user.role == "user" and current_user.country:
                source_query = source_query.filter(Order.customer_country == current_user.country)
            unique_sources = source_query.all()
            unique_sources = [source[0] for source in unique_sources]
            
            # 获取唯一的国家列表
            country_query = db.query(Order.customer_country).distinct().order_by(Order.customer_country)
            if current_user and current_user.role == "user" and current_user.country:
                country_query = country_query.filter(Order.customer_country == current_user.country)
            unique_countries = country_query.all()
            unique_countries = [country[0] for country in unique_countries if country[0]]
            
            # 初始化空的图表数据
            dates = []
            statuses = []
            chart_data = {}

            last_sync_log = db.query(SyncLog).order_by(SyncLog.synced_at.desc()).first()
            last_sync = last_sync_log.synced_at if last_sync_log else None

            # 创建分页对象模拟Flask-SQLAlchemy的paginate
            class Pagination:
                def __init__(self, page, per_page, total):
                    self.page = page
                    self.per_page = per_page
                    self.total = total
                    self.pages = (total + per_page - 1) // per_page
                    self.has_prev = page > 1
                    self.has_next = page < self.pages
                    self.prev_num = page - 1 if self.has_prev else None
                    self.next_num = page + 1 if self.has_next else None
                
                def iter_pages(self, left_edge=2, left_current=2, right_current=3, right_edge=2):
                    """生成分页链接"""
                    last = 0
                    for num in range(1, self.pages + 1):
                        if num <= left_edge or \
                           (num > self.page - left_current - 1 and num < self.page + right_current) or \
                           num > self.pages - right_edge:
                            if last + 1 != num:
                                yield None
                            yield num
                            last = num

            pagination = Pagination(page, per_page, total_orders)

            return render_template('orders.html',
                                 orders=orders,
                                 unique_sources=unique_sources,
                                 unique_countries=unique_countries,  # 添加国家列表
                                 last_sync=last_sync,
                                 status_stats=status_stats,
                                 pagination=pagination,
                                 current_page=page,
                                 per_page=per_page,
                                 total_orders=total_orders,
                                 can_add_order=can_add)  # 传递添加订单权限
        finally:
            db.close()
    
    # 保留原有的辅助方法
    @staticmethod
    def _get_site_config(source):
        """
        根据来源获取站点配置信息
        支持通过域名或用户名匹配
        """
        from config import SITES
        
        for site in SITES:
            if site['domain'] == source or site['user'] == source:
                return site
        return None
    
    @staticmethod
    def _identify_carrier(tracking_number):
        """
        根据物流单号特征识别承运商
        """
        if not tracking_number:
            return "Unknown"
        
        # 清理跟踪号码，只保留数字和字母
        cleaned_number = ''.join(c for c in tracking_number if c.isalnum())
        
        # InPost: 24位纯数字
        if len(cleaned_number) == 24 and cleaned_number.isdigit():
            return "InPost"
        
        # DPD: 10-14位数字/字母组合
        if 10 <= len(cleaned_number) <= 14 and cleaned_number.isalnum():
            return "DPD"
        
        return "Unknown"
    
    @staticmethod
    def _get_carrier_slug(tracking_number):
        """
        获取承运商的slug
        """
        carrier = OrderController._identify_carrier(tracking_number)
        if carrier == "InPost":
            return "inpost"
        elif carrier == "DPD":
            return "dpd"
        return ""
    
    @requires_role("manager")  # 只有manager和root可以添加订单
    def add_order(self):
        db = SessionLocal()
        try:
            current_user = getattr(request, 'current_user', None)
            
            if request.method == 'POST':
                # 处理表单提交
                try:
                    # 获取表单数据
                    order_id = request.form.get('order_id', '').strip()
                    source = request.form.get('source', '').strip()
                    customer_first_name = request.form.get('customer_first_name', '').strip()
                    customer_last_name = request.form.get('customer_last_name', '').strip()
                    customer_email = request.form.get('customer_email', '').strip()
                    customer_phone = request.form.get('customer_phone', '').strip()
                    customer_country = request.form.get('customer_country', '').strip()
                    customer_address = request.form.get('customer_address', '').strip()
                    customer_address_2 = request.form.get('customer_address_2', '').strip()  # 添加第二地址
                    customer_city = request.form.get('customer_city', '').strip()
                    customer_state = request.form.get('customer_state', '').strip()
                    customer_postcode = request.form.get('customer_postcode', '').strip()
                    total = float(request.form.get('total', 0))
                    status = request.form.get('status', 'processing').strip()
                    item_count = int(request.form.get('item_count', 0))
                    item_details = request.form.get('item_details', '').strip()
                    
                    # 组合客户姓名
                    customer_name = f"{customer_first_name} {customer_last_name}".strip()
                    if not customer_name:
                        customer_name = customer_email or "Unknown"
                    
                    # 从来源站点获取域名和货币信息
                    domain = ""
                    currency = "USD"  # 默认货币
                    from config import SITES
                    for site in SITES:
                        if site['user'] == source:
                            domain = site['domain']
                            # 如果站点配置中有货币信息，则使用站点配置的货币
                            # 这里可以根据实际需求进行扩展
                            break
                    
                    # 检查订单ID是否已存在
                    existing_order = db.query(Order).filter(Order.order_id == order_id).first()
                    if existing_order:
                        flash('订单ID已存在', 'error')
                        # 获取国家列表用于表单
                        country_query = db.query(Order.customer_country).distinct().order_by(Order.customer_country)
                        unique_countries = country_query.all()
                        unique_countries = [country[0] for country in unique_countries if country[0]]
                        # 获取站点配置并添加默认货币信息
                        from config import SITES
                        # 为每个站点添加默认货币信息
                        sites_with_currency = []
                        for site in SITES:
                            # 根据域名确定默认货币
                            if 'buchmistrz.pl' in site['domain']:
                                currency = 'PLN'  # 波兰兹罗提
                            elif 'strefajednorazowek.pl' in site['domain']:
                                currency = 'PLN'  # 波兰兹罗提
                            else:
                                currency = 'USD'  # 默认美元
                            site_with_currency = site.copy()
                            site_with_currency['currency'] = currency
                            sites_with_currency.append(site_with_currency)
                        return render_template('add_order.html', 
                                             error='订单ID已存在',
                                             unique_countries=unique_countries,
                                             SITES=sites_with_currency,
                                             current_user=current_user)
                    
                    # 创建新订单
                    new_order = Order(
                        order_id=order_id,
                        source=source,
                        domain=domain,  # 从来源站点获取的域名
                        customer_first_name=customer_first_name,
                        customer_last_name=customer_last_name,
                        customer_name=customer_name,
                        customer_email=customer_email,
                        customer_phone=customer_phone,
                        customer_country=customer_country,
                        customer_address=customer_address,
                        customer_address_2=customer_address_2,  # 添加第二地址
                        customer_city=customer_city,
                        customer_state=customer_state,
                        customer_postcode=customer_postcode,
                        total=total,
                        currency=currency,  # 从来源站点获取的货币或默认货币
                        order_date=dt.datetime.now(dt.UTC),
                        status=status,
                        item_count=item_count,
                        item_details=item_details
                    )
                    
                    # 添加到数据库
                    db.add(new_order)
                    db.commit()
                    
                    # 添加状态历史记录
                    status_history = StatusHistory(
                        order_id=order_id,
                        source=source,
                        old_status=None,
                        new_status=status,
                        changed_at=dt.datetime.now(dt.UTC)
                    )
                    db.add(status_history)
                    db.commit()
                    
                    flash('订单添加成功', 'success')
                    return redirect(url_for('orders'))
                    
                except Exception as e:
                    db.rollback()
                    flash(f'添加订单时出错: {str(e)}', 'error')
                    # 获取国家列表用于表单
                    country_query = db.query(Order.customer_country).distinct().order_by(Order.customer_country)
                    unique_countries = country_query.all()
                    unique_countries = [country[0] for country in unique_countries if country[0]]
                    # 获取站点配置并添加默认货币信息
                    from config import SITES
                    # 为每个站点添加默认货币信息
                    sites_with_currency = []
                    for site in SITES:
                        # 根据域名确定默认货币
                        if 'buchmistrz.pl' in site['domain']:
                            currency = 'PLN'  # 波兰兹罗提
                        elif 'strefajednorazowek.pl' in site['domain']:
                            currency = 'PLN'  # 波兰兹罗提
                        else:
                            currency = 'USD'  # 默认美元
                        site_with_currency = site.copy()
                        site_with_currency['currency'] = currency
                        sites_with_currency.append(site_with_currency)
                    return render_template('add_order.html', 
                                         error=f'添加订单时出错: {str(e)}',
                                         unique_countries=unique_countries,
                                         SITES=sites_with_currency,
                                         current_user=current_user)
            
            else:
                # 显示添加订单表单
                # 获取国家列表
                country_query = db.query(Order.customer_country).distinct().order_by(Order.customer_country)
                unique_countries = country_query.all()
                unique_countries = [country[0] for country in unique_countries if country[0]]
                
                # 获取站点配置并添加默认货币信息
                from config import SITES
                # 为每个站点添加默认货币信息和运费信息
                sites_with_additional_info = []
                for site in SITES:
                    # 根据域名确定默认货币
                    if 'buchmistrz.pl' in site['domain']:
                        currency = 'PLN'  # 波兰兹罗提
                    elif 'strefajednorazowek.pl' in site['domain']:
                        currency = 'PLN'  # 波兰兹罗提
                    else:
                        currency = 'USD'  # 默认美元
                    
                    # 获取运费信息
                    shipping_methods = self._get_shipping_methods(site)
                    
                    site_with_info = site.copy()
                    site_with_info['currency'] = currency
                    site_with_info['shipping_methods'] = shipping_methods
                    sites_with_additional_info.append(site_with_info)
                
                return render_template('add_order.html', 
                                     unique_countries=unique_countries,
                                     SITES=sites_with_additional_info,
                                     current_user=current_user)
        finally:
            db.close()
    
    @staticmethod
    def _get_shipping_methods(site_config):
        """
        获取站点的运费方法
        """
        try:
            import httpx
            # 添加浏览器模拟的请求头
            headers = {
                "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36",
                "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8",
                "Accept-Language": "en-US,en;q=0.5",
                "Accept-Encoding": "gzip, deflate, br",
                "Connection": "keep-alive",
                "Upgrade-Insecure-Requests": "1",
                "Sec-Fetch-Dest": "document",
                "Sec-Fetch-Mode": "navigate",
                "Sec-Fetch-Site": "none",
                "Cache-Control": "max-age=0"
            }
            
            # 获取运费区域
            zones_url = f"{site_config['url']}/wp-json/wc/v3/shipping/zones"
            
            with httpx.Client(timeout=30.0, http2=True, follow_redirects=True, headers=headers) as client:
                zones_response = client.get(
                    zones_url,
                    auth=(site_config["consumer_key"], site_config["consumer_secret"]),
                    headers=headers
                )
                zones_response.raise_for_status()
                zones = zones_response.json()
                
                # 获取所有区域的运费方法
                all_shipping_methods = []
                for zone in zones:
                    zone_id = zone['id']
                    methods_url = f"{site_config['url']}/wp-json/wc/v3/shipping/zones/{zone_id}/methods"
                    try:
                        methods_response = client.get(
                            methods_url,
                            auth=(site_config["consumer_key"], site_config["consumer_secret"]),
                            headers=headers
                        )
                        methods_response.raise_for_status()
                        methods = methods_response.json()
                        
                        # 提取运费方法信息
                        for method in methods:
                            if method.get('enabled', False):
                                # 处理费用值，确保是字符串格式
                                cost_value = method.get('settings', {}).get('cost', {}).get('value', '0')
                                # 如果费用是字典格式，尝试提取数值
                                if isinstance(cost_value, dict):
                                    cost_value = cost_value.get('value', '0')
                                
                                all_shipping_methods.append({
                                    'id': method.get('instance_id'),
                                    'name': method.get('title', method.get('method_title', '')),
                                    'cost': str(cost_value),
                                    'description': method.get('method_description', '')
                                })
                    except Exception as e:
                        print(f"获取区域 {zone['name']} 的运费方法时出错: {str(e)}")
                        continue
                
                return all_shipping_methods
                
            return []
        except Exception as e:
            print(f"获取运费方法时出错: {str(e)}")
            return []
    
    @requires_auth
    def order_detail(self, order_id):
        db = SessionLocal()
        try:
            # 获取订单信息
            order = db.query(Order).filter(Order.id == order_id).first()
            if not order:
                return "订单未找到", 404
            
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            if current_user and current_user.role == "user":
                if current_user.country and order.customer_country != current_user.country:
                    return "权限不足", 403
            
            # 获取状态历史
            status_history_query = db.query(StatusHistory).filter(
                StatusHistory.order_id == order.order_id,
                StatusHistory.source == order.source
            ).order_by(StatusHistory.changed_at.desc())
            
            status_history = status_history_query.all()
            
            # 提取运单物流数据
            tracking_data = []
            if order.item_details:
                try:
                    import json
                    items = json.loads(order.item_details)
                    for item in items:
                        # 检查是否是专门用于存储物流信息的虚拟商品项
                        if item.get('name') == '物流信息' and 'meta_data' in item:
                            for meta in item['meta_data']:
                                if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                    # 直接解析物流信息
                                    tracking_info_value = meta.get('value', '[]')
                                    # 检查是否已经是解析后的对象而不是JSON字符串
                                    if isinstance(tracking_info_value, str):
                                        tracking_info = json.loads(tracking_info_value)
                                    else:
                                        tracking_info = tracking_info_value
                                    
                                    if isinstance(tracking_info, list):
                                        tracking_data.extend(tracking_info)
                                    elif isinstance(tracking_info, dict):
                                        tracking_data.append(tracking_info)
                        # 处理普通商品项中的物流信息
                        elif 'meta_data' in item:
                            for meta in item['meta_data']:
                                if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                    tracking_info = json.loads(meta.get('value', '[]'))
                                    if isinstance(tracking_info, list):
                                        tracking_data.extend(tracking_info)
                                    elif isinstance(tracking_info, dict):
                                        tracking_data.append(tracking_info)
                except Exception as e:
                    print(f"解析运单数据时出错: {e}")
            
            # 移除了对旧数据方式的兼容性支持
            # 不再尝试从其他数据源获取物流信息
            
            # 格式化运单时间并添加追踪链接 - 保持与eshop.php的兼容性
            for tracking in tracking_data:
                # 格式化时间
                if 'time' in tracking and tracking['time']:
                    from datetime import datetime
                    try:
                        tracking['formatted_time'] = datetime.fromtimestamp(tracking['time']).strftime('%Y-%m-%d %H:%M:%S')
                    except:
                        tracking['formatted_time'] = '-'
                else:
                    tracking['formatted_time'] = '-'
                
                # 添加追踪链接 - 与eshop.php保持一致的处理逻辑
                tracking_number = tracking.get('tracking_number', '')
                carrier_slug = tracking.get('carrier_slug', '').lower()
                carrier_name = tracking.get('carrier_name', '').lower()
                
                if tracking_number:
                    # 根据承运商生成追踪链接 - 与eshop.php中的逻辑保持一致
                    if 'carrier_url' in tracking and '{tracking_number}' in tracking['carrier_url']:
                        # 使用从WooCommerce获取的carrier_url生成链接
                        tracking['tracking_url'] = tracking['carrier_url'].replace('{tracking_number}', tracking_number)
                    elif carrier_slug == 'inpost' or carrier_name == 'inpost':
                        # InPost: 24位纯数字运单号 - 与eshop.php保持一致
                        tracking['tracking_url'] = f'https://inpost.pl/sledzenie-przesylek?number={tracking_number}'
                    elif carrier_slug == 'dpd' or carrier_name == 'dpd':
                        # DPD: 10-14位数字/字母组合运单号 - 与eshop.php保持一致
                        tracking['tracking_url'] = f'https://tracktrace.dpd.com.pl/parcelDetails?typ=1&p1={tracking_number}'
                    else:
                        # 默认不生成链接，保持与eshop.php的一致性
                        tracking['tracking_url'] = ''
                else:
                    tracking['tracking_url'] = ''
            
            return render_template('order_detail.html', order=order, status_history=status_history, tracking_data=tracking_data)
        finally:
            db.close()
    
    @requires_auth
    def export_csv(self):
        source = request.args.get('source')
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 修复查询语法
            query = db.query(Order).order_by(Order.order_date.desc())
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            if source:
                query = query.filter(Order.source == source)
            orders = query.all()
        finally:
            db.close()

        # 使用UTF-8编码创建CSV
        output = StringIO()
        writer = csv.writer(output)
        writer.writerow(['Order ID', 'Source', 'Domain', 'Customer Name', 'First Name', 'Last Name', 'Email', 'Phone', 'Country', 'Total', 'Currency', 'Order Date', 'Status', 'Item Count', 'Item Total', 'Shipping Total', 'Item Details', 'Created At'])
        for o in orders:
            # 格式化手机号为国际格式
            formatted_phone = o.customer_phone or ''
            if formatted_phone and o.customer_country:
                formatted_phone = format_phone_number(formatted_phone, o.customer_country)
            
            writer.writerow([
                o.order_id, o.source, o.domain, o.customer_name,
                o.customer_first_name, o.customer_last_name, o.customer_email, formatted_phone,
                o.customer_country, o.total, o.currency,
                o.order_date.isoformat() if o.order_date else '',
                o.status, o.item_count, o.item_total, o.shipping_total,
                o.item_details,
                o.created_at.isoformat() if o.created_at else ''
            ])

        # 创建响应并设置正确的编码
        response = make_response(output.getvalue())
        response.headers["Content-Disposition"] = f"attachment; filename=orders_{source or 'all'}_{datetime.now().strftime('%Y%m%d')}.csv"
        response.headers["Content-Type"] = "text/csv; charset=utf-8"
        return response
    
    @requires_auth
    def api_orders(self):
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        # 获取搜索筛选参数
        order_id_search = request.args.get('order_id', '')
        country_search = request.args.get('country', '')  # 改为国家筛选
        source_search = request.args.get('source', '')
        status_search = request.args.get('status', '')
        
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 应用搜索筛选条件
            if order_id_search:
                query = query.filter(Order.order_id.contains(order_id_search))
            if country_search:  # 改为国家筛选
                query = query.filter(Order.customer_country == country_search)
            if source_search:
                query = query.filter(Order.source.contains(source_search))
            if status_search:
                query = query.filter(Order.status == status_search)
            
            # 计算总数和分页
            total_orders = query.count()
            total_pages = (total_orders + per_page - 1) // per_page
            page = max(1, min(page, total_pages))  # 确保页码在有效范围内
            offset = (page - 1) * per_page
            
            # 分页查询订单
            orders = query.order_by(Order.order_date.desc()).offset(offset).limit(per_page).all()
            
            # 为每个订单提取物流信息
            orders_data = []
            for order in orders:
                # 提取运单物流数据
                tracking_data = []
                order_items = []
                if order.item_details:
                    try:
                        import json
                        from collections import defaultdict
                        items = json.loads(order.item_details)
                        
                        # 使用字典来合计同一商品的数量
                        item_summary = defaultdict(lambda: {'name': '', 'total_quantity': 0, 'meta_data': []})
                        
                        for item in items:
                            # 检查是否是专门用于存储物流信息的虚拟商品项
                            if item.get('name') == '物流信息' and 'meta_data' in item:
                                for meta in item['meta_data']:
                                    if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                        # 直接解析物流信息
                                        tracking_info_value = meta.get('value', '[]')
                                        # 检查是否已经是解析后的对象而不是JSON字符串
                                        if isinstance(tracking_info_value, str):
                                            tracking_info = json.loads(tracking_info_value)
                                        else:
                                            tracking_info = tracking_info_value
                                        
                                        if isinstance(tracking_info, list):
                                            tracking_data.extend(tracking_info)
                                        elif isinstance(tracking_info, dict):
                                            tracking_data.append(tracking_info)
                            # 处理普通商品项
                            else:
                                # 获取商品ID（使用商品名称作为ID，因为原始数据中可能没有真正的ID）
                                item_id = item.get('name', '') + '_' + str(item.get('id', ''))
                                
                                # 合计数量
                                item_summary[item_id]['name'] = item.get('name', '')
                                item_summary[item_id]['total_quantity'] += item.get('quantity', 0)
                                
                                # 收集口味等元数据（只保留第一次出现的）
                                if 'meta_data' in item and not item_summary[item_id]['meta_data']:
                                    for meta in item['meta_data']:
                                        # 提取口味信息（支持更多口味字段）
                                        if meta.get('key') in ['pa_taste', '口味', 'taste', 'pa_flavors', 'flavors', 'pa_flavor', 'flavor', 'pa_smaki', 'smaki', 'pa_smak', 'smak']:
                                            item_summary[item_id]['meta_data'].append({
                                                'key': meta.get('key', ''),
                                                'value': meta.get('display_value', meta.get('value', ''))
                                            })
                                
                                # 提取物流信息
                                if 'meta_data' in item:
                                    for meta in item['meta_data']:
                                        if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                            tracking_info = json.loads(meta.get('value', '[]'))
                                            if isinstance(tracking_info, list):
                                                tracking_data.extend(tracking_info)
                                            elif isinstance(tracking_info, dict):
                                                tracking_data.append(tracking_info)
                        
                        # 转换为列表格式
                        for item_id, item_info in item_summary.items():
                            order_items.append({
                                'name': item_info['name'],
                                'quantity': item_info['total_quantity'],
                                'meta_data': item_info['meta_data']
                            })
                    except Exception as e:
                        print(f"解析订单数据时出错: {e}")
                
                orders_data.append({
                    'id': order.id,
                    'order_id': order.order_id,
                    'source': order.source,
                    'domain': order.domain,
                    'customer_first_name': order.customer_first_name or '',  # 添加名字段
                    'customer_last_name': order.customer_last_name or '',    # 添加姓字段
                    'customer_name': order.customer_name or '',
                    'customer_email': order.customer_email or '',
                    'customer_phone': order.customer_phone or '',
                    'customer_country': order.customer_country or '',
                    'customer_address': order.customer_address or '',
                    'customer_city': order.customer_city or '',
                    'customer_state': order.customer_state or '',
                    'customer_postcode': order.customer_postcode or '',
                    'total': order.total or 0,
                    'currency': order.currency or 'USD',
                    'order_date': order.order_date.isoformat() if order.order_date else None,
                    'status': order.status or 'processing',
                    'item_count': order.item_count or 0,
                    'items': order_items,  # 添加商品信息
                    'tracking_data': tracking_data  # 添加物流信息
                })
            
            # 获取状态统计数据
            status_stats_query = db.query(Order.status, func.count(Order.id).label('count')).group_by(Order.status)
            if current_user and current_user.role == "user" and current_user.country:
                status_stats_query = status_stats_query.filter(Order.customer_country == current_user.country)
            status_stats = status_stats_query.all()
            
            # 获取已退款订单数量
            refunded_orders_query = db.query(Order).filter(Order.status == 'refunded')
            if current_user and current_user.role == "user" and current_user.country:
                refunded_orders_query = refunded_orders_query.filter(Order.customer_country == current_user.country)
            refunded_orders_count = refunded_orders_query.count()
            
            # 在状态统计中添加已退款订单
            status_counts_dict = {stat.status: stat.count for stat in status_stats}
            # 只有当存在已退款订单时才添加到统计中
            if refunded_orders_count > 0:
                status_counts_dict['refunded'] = refunded_orders_count
            
            return jsonify({
                'orders': orders_data,
                'count': total_orders,
                'current_page': page,
                'total_pages': total_pages,
                'has_next': page < total_pages,
                'has_prev': page > 1,
                'status_counts': status_counts_dict,
                'refunded_orders_count': refunded_orders_count  # 添加已退款订单统计
            })
        finally:
            db.close()